package jdbc.batch_;

import jdbc.utils.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author DCSGO
 * @version 1.0
 * 批量处理sql语句
 */
public class Batch_ {
    @Test
    public void noBatch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "admin_" + i);
            preparedStatement.setString(2, "pwd_" + i);
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("不使用批处理，耗时：" + (end - start) + " ms");
        //不使用批处理，耗时：2436 ms
        JDBCUtils.close(null, preparedStatement, connection);
    }

    //要使用批处理就必须在 url 中添加 ?rewriteBatchedStatements=true
    //不然就没有批处理的效果
    @Test
    public void batch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        /*
        public void addBatch() throws SQLException {
            synchronized(this.checkClosed().getConnectionMutex()) {
                if (this.batchedArgs == null) {//第一次就创建 ArrayList - elementData -> Object[]
                    this.batchedArgs = new ArrayList();
                }

                //对sql语句进行检查
                for(int i = 0; i < this.parameterValues.length; ++i) {
                    this.checkAllParametersSet(this.parameterValues[i], this.parameterStreams[i], i);
                }
                //elementData -> Object[] 就会存放我们预处理的sql语句
                this.batchedArgs.add(new PreparedStatement.BatchParams(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull));
            }
        }
         */
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "admin_" + i);
            preparedStatement.setString(2, "pwd_" + i);
            preparedStatement.addBatch();//添加要批处理的 sql 语句
            if ((i + 1) % 1000 == 0) {//每1000条sql语句执行一次批处理
                preparedStatement.executeBatch();//执行批处理
                preparedStatement.clearBatch();//清空批处理sql语句
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("使用批处理，耗时：" + (end - start) + " ms");
        //使用批处理，耗时：133 ms
        JDBCUtils.close(null, preparedStatement, connection);
        //批量处理会减少我们发送sql语句的网络开销，而且减少编译次数，因此效率提高
    }
}
